using CarteConnection;
using MongoDB.Bson;
using MongoDB.Driver;
using Sustainalytics.ActiveMQHelper;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.ClientDW.Synchronization;
using Sustainalytics.ClientDW.Synchronization.RiskRatingsBenchmarks;
using Sustainalytics.ClientDW.Synchronization.SyncCarbonData;
using Sustainalytics.ClientDW.Synchronization.SyncESGData;
using Sustainalytics.ClientDW.Synchronization.SyncHbaseData;
using Sustainalytics.ClientDW.Synchronization.SyncProductInvolvement;
using Sustainalytics.ClientDW.Synchronization.SyncReferenceUniv;
using Sustainalytics.ClientDW.Synchronization.SyncRiskRatingsData;
using Sustainalytics.ClientDW.Tracing;
using Sustainalytics.CRT.Synchronization;
using Sustainalytics.DataLayer;
using Sustainalytics.DataLayer.Shared;
using Sustainalytics.Entities;
using Sustainalytics.Entities.ConnectionStrings;
using Sustainalytics.Entities.ESGRatings;
using Sustainalytics.ESGRatings.BusinessLogic;
using Sustainalytics.ESGRatings.BusinessLogic.ESGClient;
using Sustainalytics.GCCS.CompanyReportSyncStep;
using Sustainalytics.Governance.Synchronization;
using Sustainalytics.PregenerateScreeningReports;
using Sustainalytics.ProductInvolvement.BL;
using Sustainalytics.ReportGenerator.BL;
using Sustainalytics.ScreeningTool.Synchronization;
using Sustainalytics.SecurityListsSync;
using Sustainalytics.Synchronization.Interface;
using Sustainalytics.Synchronizations.ValidationSyncStep;
using Sustainalytics.Universes.Synchronization;
using Sustainalytics.Utils;
using Sustainalytics.Utils.Configuration;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Configuration.Abstractions;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Net.Mail;
using System.Net.Mime;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using ConfigurationErrorsException = System.Configuration.ConfigurationErrorsException;

namespace Sustainalytics.Synchronization.BusinessLogic
{
    public class SyncronizationEngine
    {
        private static string _gaMongoDbConnectionString;
        private static string _dsoMongoDbConnectionString;

        private static Dictionary<string, string> _gaConnectionStrings;
        private static Dictionary<string, string> _dsoConnectionStrings;

        private static IClientDWMasterRepository _clientDwMasterRepository;

        private static string _smtpServer;
        private static int _smtpServerPort;
        private static string _smtpUserName;
        private static string _smtpPassword;
        private static int _AttachmentArchivingThresholdKB;

        private static Timer _syncTimer;

        private static DateTime _initialRecurringDate;
        private static string _skipCloning;
        private static TimeSpan _regularRepeatInterval;

        private static DateTime? _lastSyncTime;
        private static bool _isSynchronizing;

        private static CancellationTokenSource _tokenSource;
        private static readonly object SyncLock = new object();

        private static SyncLog _syncLog;
        private static double _progress;
        private static LastSessionStatus _lastSessionStatus;
        private static SyncOperationResult _result;
        private static ErrorPolicy _errorPolicy;
        private static bool _isSyncAutomatic;
        private static bool _isInitialSync;
        private static bool _sessionAutoStarted;
        private static List<Tuple<SyncMessageType, string>> _allMessages;
        private static SyncContext _syncContext;

        private static SyncronizationSessionDTO _lastSession;

        private static int _MaxSyncStepExecutionAttempts = 5;
        private static int _SyncStepRetryInitialInterval = 10;


        // MongoDB connections string type
        private const string MongoDBConnectionString = "MongoDBConnectionString";
        private const string ClientDWMaster = "ClientDWMaster";
        private const string ClientPlatform = "ClientPlatform";

        private const string RPHistoricalDataSet = "RPHistoricalDataSet";
        private const string GovernanceInputFile = "GovernanceInputFile";
        private const string GovernanceDW = "GovernanceDW";
        private const string GovernanceCompanyReportPDF = "GovernanceCompanyReportPDF";


        // SQL Server connections string type
        private const string RpConnectionType = "RPConnectionString";
        private const string RpConnectionTempType = "RPConnectionString_Temp";
        private const string EventsConnectionType = "EventsConnectionString";
        private const string GccsConnectionType = "GCCSConnectionString";
        private const string GovernanceConnectionType = "GovernanceConnectionString";
        private const string FocusedRatingsConnectionType = "FocusedRatingsConnectionString";


        private static DatabaseGroup _lastDatabaseGroup;
        private static DatabaseGroup _currentDatabaseGroup;
        private static string _machineName;

        private static SyncServiceStatus _serviceStatus;

        private static List<SyncStepDescription> _sortedUserConfiguredSteps = null;

        private static readonly Dictionary<SyncStepOrder, ISyncStep> StepDictionary = new Dictionary<SyncStepOrder, ISyncStep>()
        {
            { SyncStepOrder.SyncCorporateData,                new SyncCorporateData()                          },
            { SyncStepOrder.SyncSecuritiesLists,              new SyncHBaseSecurityLists()                     },
            { SyncStepOrder.SyncReferenceUniverses,           new SyncReferenceUniverses()                     },
            { SyncStepOrder.UniversesFromCrm,                 new UniversesFromCrmSyncStep()                   },
            { SyncStepOrder.LoadFeatureUniverses,             new LoadFeatureUniversesSyncStep()               },
            { SyncStepOrder.SyncControversies,                new ControversiesSyncStep()                      },
            { SyncStepOrder.CompanyWeights,                   new ESGRatingsCompanyWeightsSyncStep()           },
            { SyncStepOrder.SyncEsgData,                      new EsgDataSyncStep()                            },
            { SyncStepOrder.SyncGlobalCompact,                new GlobalCompactSyncStep()                      },
            { SyncStepOrder.SyncGovernance,                   new GovernanceSyncStep()                         },
            { SyncStepOrder.SyncGovernanceComputation,        new GovernanceComputationSyncStep()              },
            { SyncStepOrder.ESGRatingsDataValidation,         new ESGRatingsPreparationSyncStep()              },
            { SyncStepOrder.CarbonData,                       new CarbonDataSyncStep()                         },
            { SyncStepOrder.PITraditional,                    new PITraditionalSyncStep()                      },
            { SyncStepOrder.PISolutions,                      new PISolutionsSyncStep()                        },
            { SyncStepOrder.AssureFeatureUniverses,           new AssureFeatureUniversesSyncStep()             },
            { SyncStepOrder.ReportDataPreparation,            new ReportDataPreparationSyncStep()              },
            { SyncStepOrder.FocusedRatingsComputationOutput2, new FocusedRatingsComputationSyncStep2()         },
            { SyncStepOrder.SyncHistoricalData,               new HistoricalDataSyncStep()                     },
            { SyncStepOrder.DataValidation,                   new ValidationSyncStep()                         },
            { SyncStepOrder.ScreeningData,                    new ScreeningSyncStep()                          },
            { SyncStepOrder.PregenerateScreeningReports,      new PregenerateStandardReportsSyncStep()         },
            { SyncStepOrder.ControversyAlerts,                new ControversyAlertsSyncStep()                  },
            { SyncStepOrder.CleanupS3,                        new S3CleanupSyncStep()                          },
            { SyncStepOrder.GeneratePDFReports,               new GeneratePdfReportSyncStep()                  },
            { SyncStepOrder.ProcessedFiles,                   new ProcessedFilesSyncStep()                     },
            { SyncStepOrder.SyncRiskRatingsData,              new RiskRatingsDataSyncStep()                    },
            { SyncStepOrder.SyncRiskRatingsBenchmarks,        new RiskRatingsBenchmarksSyncStep()              },
        };

        private static string _cartePath = ConfigurationManager.Instance.AppSettings["CartePath"];
        private static string _carteMessage = ConfigurationManager.Instance.AppSettings["CarteMessage"];

        public SyncronizationEngine()
        {
            LoadConfigSettingsAndInitMembers();

            _allMessages = new List<Tuple<SyncMessageType, string>>();
            _lastDatabaseGroup = LoadLastDatabaseGroup();
            _lastSessionStatus = Sustainalytics.ClientDW.Entities.LastSessionStatus.Unknown;
            _progress = 0.0;
            _isSynchronizing = false;
            _lastSyncTime = null;
            _serviceStatus = SyncServiceStatus.Dormant;
            _sessionAutoStarted = false;
            _machineName = Environment.MachineName.ToUpperInvariant();

            // start timer
            if (_isSyncAutomatic)
            {
                _syncTimer = new Timer(DailyRecurringTimer, null, 10 * 1000, 1 * 60 * 1000);
            }

            var s = "SyncronizationEngine ok";
            EventLog.WriteEntry("Sustainalytics.Synchronization.Service", s, EventLogEntryType.Information);
            Console.WriteLine(s);
        }

        private static DatabaseGroup LoadLastDatabaseGroup()
        {
            var lastSyncLog = _clientDwMasterRepository.GetLastSync(false);
            if (lastSyncLog != null)
            {
                if (lastSyncLog.TimeEnd != null)
                    _lastSyncTime = lastSyncLog.TimeEnd;

                return lastSyncLog.DatabaseGroup;
            }

            return DatabaseGroup.None;
        }

        private void LoadConfigSettingsAndInitMembers()
        {
            if (ConfigurationManager.Instance.ConnectionStrings["GAMongoDBConnectionString"] == null)
                throw new Exception("GAMongoDBConnectionString key is missing!");

            _gaMongoDbConnectionString = ConfigurationManager.Instance.ConnectionStrings["GAMongoDBConnectionString"].ConnectionString;
            _clientDwMasterRepository = ClientDWMasterRepository.GetInstance(_gaMongoDbConnectionString);

            if (ConfigurationManager.Instance.ConnectionStrings["DSOMongoDBConnectionString"] == null)
                throw new Exception("DSOMongoDBConnectionString key is missing!");

            _dsoMongoDbConnectionString = ConfigurationManager.Instance.ConnectionStrings["DSOMongoDBConnectionString"].ConnectionString;
            _errorPolicy = ConfigurationManager.Instance.AppSettings.AppSettingSilent("ErrorPolicy", () => ErrorPolicy.BreakRightAway);
            SyncHub.HubContext.Clients.All.errrorPolicy(CurrentErrorPolicy());

            _smtpServer = ConfigurationManager.Instance.AppSettings.AppSetting("SMTPServer", () => throw new ConfigurationErrorsException("SMTP Server address key is missing!"));
            _smtpServerPort = ConfigurationManager.Instance.AppSettings.AppSetting<Int32>("SMTPServerPort", () => throw new ConfigurationErrorsException("SMTP Server port key is missing!"));
            _smtpUserName = ConfigurationManager.Instance.AppSettings.AppSetting("SMTPUserName", () => throw new ConfigurationErrorsException("SMTP UserName key is missing!"));
            _smtpPassword = ConfigurationManager.Instance.AppSettings.AppSetting("SMTPPassword", () => throw new ConfigurationErrorsException("SMTP Password key is missing!"));
            _AttachmentArchivingThresholdKB = ConfigurationManager.Instance.AppSettings.AppSettingSilent("AttachmentArchivingThresholdKB", () => 500);


            _isSyncAutomatic = ConfigurationManager.Instance.AppSettings.AppSetting<bool>("IsSyncAutomatic", () => throw new ConfigurationErrorsException("IsSyncAutomatic key is missing!"));
            _isInitialSync = ConfigurationManager.Instance.AppSettings.AppSetting<bool>("IsInitialSync", () => throw new ConfigurationErrorsException("IsInitialSync key is missing!"));
            _MaxSyncStepExecutionAttempts = ConfigurationManager.Instance.AppSettings.AppSettingSilent("MaxSyncStepExecutionAttempts", () => 5);
            _SyncStepRetryInitialInterval = ConfigurationManager.Instance.AppSettings.AppSettingSilent("SyncStepRetryInitialInterval", () => 10);

            _initialRecurringDate = ConfigurationManager.Instance.AppSettings.AppSetting(
                "InitialRecurringDate",
                () => throw new ConfigurationErrorsException("InitialRecurringDate key is missing!"),
                () => DateTime.UtcNow);

            _regularRepeatInterval = ConfigurationManager.Instance.AppSettings.AppSetting(
                "RegularRepeatInterval",
                () => throw new ConfigurationErrorsException("RegularRepeatInterval key is missing!"),
                () => TimeSpan.FromDays(1));

            _skipCloning = ConfigurationManager.Instance.AppSettings["SkipClonning"];

            UpdateAutomaticRunDateTime();
        }


        private static void SetDSOConnectionStrings()
        {
            _dsoConnectionStrings = new Dictionary<string, string>
            {
                {MongoDBConnectionString, _dsoMongoDbConnectionString},
                {ClientPlatform, string.Format("{0}/{1}", _dsoMongoDbConnectionString, ClientPlatform)}
            };

            SetMongoDbDSOConnectionString(ConnectionStringType.ScreeningDW);
        }

        private static void SetGAConnectionStrings()
        {
            _gaConnectionStrings = new Dictionary<string, string>
            {
                {MongoDBConnectionString, _gaMongoDbConnectionString},
                {ClientDWMaster, string.Format("{0}/{1}", _gaMongoDbConnectionString, ClientDWMaster)},
                {ClientPlatform, string.Format("{0}/{1}", _gaMongoDbConnectionString, ClientPlatform)},
                {RPHistoricalDataSet, string.Format("{0}/{1}", _gaMongoDbConnectionString, RPHistoricalDataSet)},
                {GovernanceInputFile, string.Format("{0}/{1}", _gaMongoDbConnectionString, GovernanceInputFile)},
            };

            SetMongoDbGAConnectionString(ConnectionStringType.ClientDW);
            SetMongoDbGAConnectionString(ConnectionStringType.CompanyReportPDF);
            SetMongoDbGAConnectionString(ConnectionStringType.ControversyReportPDF);

            SetMongoDbGAConnectionString(ConnectionStringType.ClientUniverses);
            SetMongoDbGAConnectionString(ConnectionStringType.DWArchive);
            SetMongoDbGAConnectionString(ConnectionStringType.ScreeningDW);
            SetMongoDbGAConnectionString(ConnectionStringType.RiskRatingsResearchDW);
            SetMongoDbGAConnectionString(ConnectionStringType.ProductInvolvementDW);
            SetMongoDbGAConnectionString(GovernanceDW);
            SetMongoDbGAConnectionString(GovernanceCompanyReportPDF);

            SetSqlConnectionString(ConnectionStringType.CRM.ToString());
            SetSqlConnectionString(RpConnectionType);
            SetSqlConnectionString(RpConnectionTempType);
            SetSqlConnectionString(EventsConnectionType);
            SetSqlConnectionString(GccsConnectionType);
            SetSqlConnectionString(FocusedRatingsConnectionType);

            SetSqlConnectionString(GovernanceConnectionType);

            SetUpAppSetting("RedisConnectionString");
            SetUpAppSetting("RedisDatabaseId");

            SetUpAppSetting("HBaseUrl");
            SetUpAppSetting("HBaseUsername");
            SetUpAppSetting("HBasePassword");

            _gaConnectionStrings.Add("ComputationServiceAddress",
                ConfigurationManager.Instance.AppSettings.AppSetting("ComputationServiceAddress",
                () => throw new ConfigurationErrorsException("The ComputationServiceAddress is missing."),
                () => "http://localhost:10280/esgcomputingserv/api/v1"));
        }


        private static void SetMongoDbDSOConnectionString(ConnectionStringType connectionStringType)
        {
            var connectionString = string.Format("{0}/{1}_{2}", _dsoMongoDbConnectionString, connectionStringType, _currentDatabaseGroup);

            if (ConfigurationManager.Instance.AppSettings["DatabaseType"] == "replica")
            {
                connectionString = string.Format("{0}/{1}_{2}?connect=replicaset;replicaSet={3}",
                    _dsoMongoDbConnectionString, connectionStringType, _currentDatabaseGroup, ConfigurationManager.Instance.AppSettings["ReplicaSetName"]);
            }

            _dsoConnectionStrings.Add(connectionStringType.ToString(), connectionString);
        }

        private static void SetMongoDbGAConnectionString(ConnectionStringType connectionStringType)
        {
            SetMongoDbGAConnectionString(connectionStringType.ToString());
        }

        private static void SetMongoDbGAConnectionString(string databaseName)
        {
            var connectionString = string.Format("{0}/{1}_{2}", _gaMongoDbConnectionString, databaseName, _currentDatabaseGroup);
            if (ConfigurationManager.Instance.AppSettings["DatabaseType"] == "replica")
            {
                connectionString = string.Format("{0}/{1}_{2}?connect=replicaset;replicaSet={3}",
                    _gaMongoDbConnectionString, databaseName, _currentDatabaseGroup, ConfigurationManager.Instance.AppSettings["ReplicaSetName"]);
            }

            _gaConnectionStrings.Add(databaseName, connectionString);
        }


        private static void SetSqlConnectionString(string connectionStringType)
        {
            if (ConfigurationManager.Instance.ConnectionStrings[connectionStringType] == null)
                throw new ArgumentException(string.Format("{0} key is missing!", connectionStringType));

            var connectionString = ConfigurationManager.Instance.ConnectionStrings[connectionStringType].ConnectionString;

            _gaConnectionStrings.Add(connectionStringType, connectionString);
        }

        private static void SetUpAppSetting(string appSettingKey)
        {
            if (ConfigurationManager.Instance.AppSettings[appSettingKey] == null)
            {
                throw new ArgumentException(string.Format("{0} key is missing from appSettings!", appSettingKey));
            }
            _gaConnectionStrings.Add(appSettingKey, ConfigurationManager.Instance.AppSettings[appSettingKey]);
        }



        public static string GetConnectionString(ProductType productType, ConnectionStringType connectionStringType, DatabaseGroup dbGroup)
        {
            var connectionString = _gaConnectionStrings["ClientPlatform"];
            if (productType == ProductType.DSO)
                connectionString = _dsoConnectionStrings["ClientPlatform"];

            var newConnectionString = connectionString.Replace("ClientPlatform",
                string.Format("{0}_{1}", connectionStringType, dbGroup));

            return newConnectionString;
        }

        private static Dictionary<string, string> GetCurrentConnectionStrings()
        {
            var connectionString = _gaConnectionStrings["ClientPlatform"];
            var collection =
                MongoDatabaseFactory.GetDatabase(connectionString)
                    .GetCollection<ConnectionStrings>(typeof(ConnectionStrings).Name);

            var filter = new BsonDocument();

            var oldConnectionStrings = collection.Find(filter).SingleOrDefault();

            var oldDictionary = new Dictionary<string, string>();
            foreach (var db in Enum.GetValues(typeof(ConnectionStringType)))
            {
                var propertyInfo = oldConnectionStrings.GetType().GetProperty(db.ToString());
                if (propertyInfo != null)
                {
                    var connectionStringValue =
                        (string)propertyInfo.GetValue(oldConnectionStrings, null);
                    if (!string.IsNullOrEmpty(connectionStringValue))
                    {
                        oldDictionary.Add(db.ToString(), connectionStringValue);
                    }
                }
            }

            return oldDictionary;
        }


        public static void SetConnectionStrings(ProductType productType, DatabaseGroup dbGroup)
        {
            var connectionString = _gaConnectionStrings["ClientPlatform"];
            if (productType == ProductType.DSO)
                connectionString = _dsoConnectionStrings["ClientPlatform"];


            var collection = MongoDatabaseFactory.GetDatabase(connectionString)
                .GetCollection<ConnectionStrings>(typeof(ConnectionStrings).Name);


            var filter = new BsonDocument();

            var oldConnectionStrings = collection.Find(filter).SingleOrDefault();


            collection.DeleteMany(filter);

            var newConnectionStrings = new ConnectionStrings()
            {
                Timestamp = DateTime.UtcNow,
                DatabaseGroup = dbGroup.ToString(),
                ScreeningDW = GetConnectionString(productType, ConnectionStringType.ScreeningDW, dbGroup),
            };

            if (productType == ProductType.GA)
            {
                newConnectionStrings.ClientDW = GetConnectionString(productType, ConnectionStringType.ClientDW, dbGroup);
                newConnectionStrings.ClientUniverses = GetConnectionString(productType, ConnectionStringType.ClientUniverses, dbGroup);
                newConnectionStrings.GovernanceDW = GetConnectionString(productType, ConnectionStringType.GovernanceDW, dbGroup);
                newConnectionStrings.GovernanceCompanyReportPDF = GetConnectionString(productType, ConnectionStringType.GovernanceCompanyReportPDF, dbGroup);
                newConnectionStrings.DWArchive = GetConnectionString(productType, ConnectionStringType.DWArchive, dbGroup);
                newConnectionStrings.CompanyReportPDF = GetConnectionString(productType, ConnectionStringType.CompanyReportPDF, dbGroup);
                newConnectionStrings.ControversyReportPDF = GetConnectionString(productType, ConnectionStringType.ControversyReportPDF, dbGroup);
                newConnectionStrings.RiskRatingsResearchDW = GetConnectionString(productType, ConnectionStringType.RiskRatingsResearchDW, dbGroup);
                newConnectionStrings.ProductInvolvementDW = GetConnectionString(productType, ConnectionStringType.ProductInvolvementDW, dbGroup);
            }

            if (oldConnectionStrings != null)
            {
                WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                    productType, ConnectionStringType.ScreeningDW, oldConnectionStrings.ScreeningDW, newConnectionStrings.ScreeningDW));

                if (productType == ProductType.GA)
                {
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                        productType, ConnectionStringType.ClientDW, oldConnectionStrings.ClientDW, newConnectionStrings.ClientDW));
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                        productType, ConnectionStringType.ClientUniverses, oldConnectionStrings.ClientUniverses, newConnectionStrings.ClientUniverses));
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                        productType, ConnectionStringType.GovernanceDW, oldConnectionStrings.GovernanceDW, newConnectionStrings.GovernanceDW));
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                        productType, ConnectionStringType.GovernanceCompanyReportPDF, oldConnectionStrings.GovernanceCompanyReportPDF, newConnectionStrings.GovernanceDW));
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                        productType, ConnectionStringType.GovernanceCompanyReportPDF, oldConnectionStrings.DWArchive, newConnectionStrings.DWArchive));
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                       productType, ConnectionStringType.RiskRatingsResearchDW, oldConnectionStrings.RiskRatingsResearchDW, newConnectionStrings.RiskRatingsResearchDW));
                    WriteInListAndHub(string.Format("Updating {0} connection string {1} which has the current value {2} with new value {3}",
                       productType, ConnectionStringType.ProductInvolvementDW, oldConnectionStrings.ProductInvolvementDW, newConnectionStrings.ProductInvolvementDW));
                }
            }

            collection.InsertOne(newConnectionStrings);
        }


        public static bool TrySetConnectionStrings(ProductType productType, DatabaseGroup dbGroup)
        {
            var result = true;

            for (var retry = 0; retry < _MaxSyncStepExecutionAttempts; retry++)
            {
                Thread.Sleep(TimeSpan.FromSeconds(_SyncStepRetryInitialInterval * retry));

                try
                {
                    SetConnectionStrings(productType, dbGroup);
                    break;
                }
                catch (Exception ex)
                {
                    WriteInListAndHub(ex.Message);

                    result = false;
                }
            }

            return result;
        }

        public static void TryUpdateConnectionStringsInClientPlatform(bool syncDsoDatabase)
        {
            var gaUpdate = TrySetConnectionStrings(ProductType.GA, _currentDatabaseGroup);

            if (!gaUpdate)
            {
                WriteInListAndHub("Update of GA connection strings failed!");
                _result = SyncOperationResult.Fail;
                return;
            }

            WriteInListAndHub(string.Format("The {0} connection strings was successfully updated.", ProductType.GA));


            if (!syncDsoDatabase) return;


            var dsoUpdate = TrySetConnectionStrings(ProductType.DSO, _currentDatabaseGroup);

            if (!dsoUpdate)
            {
                _result = SyncOperationResult.Fail;

                WriteInListAndHub("Update of DSO connection strings failed!");

                // Rollback the update of GA connection strings

                gaUpdate = TrySetConnectionStrings(ProductType.GA, _lastDatabaseGroup);

                if (!gaUpdate)
                {
                    WriteInListAndHub("Restore of GA connection strings failed!");
                }

                WriteInListAndHub("Restore of GA connection strings was succesfull.");

                return;
            }

            WriteInListAndHub(string.Format("The {0} connection strings was successfully updated.", ProductType.DSO));
        }


        private static void DailyRecurringTimer(object state)
        {
            lock (SyncLock)
            {
                if (DateTime.UtcNow < _initialRecurringDate)
                    return;

                if (_isSynchronizing)
                    return;
            }

            StartSync(true);
        }


        // getters for UI

        public string NextDateForAutomaticSync()
        {
            return _initialRecurringDate.ToString(CultureInfo.InvariantCulture);
        }
        public string CurrentErrorPolicy()
        {
            return _errorPolicy.ToString();
        }

        public string CurrentDatabaseGroup()
        {
            UpdateCurrentDatabaseGroup();
            return _currentDatabaseGroup.ToString();
        }
        public string LastDatabaseGroup()
        {
            _lastDatabaseGroup = LoadLastDatabaseGroup();
            return _lastDatabaseGroup.ToString();
        }
        public string NextAutoStart()
        {
            if (_isSyncAutomatic)
                return _initialRecurringDate.ToString(CultureInfo.InvariantCulture);
            return "N/A";
        }

        public string SkipCloning()
        {
            return _skipCloning;
        }

        public bool IsSyncAutomatic()
        {
            return _isSyncAutomatic;
        }

        public string GetMachineName()
        {
            return _machineName;
        }

        public bool IsSynchronizing()
        {
            return _isSynchronizing;
        }

        public string ServiceStatus()
        {
            return _serviceStatus.ToString();
        }

        public string GetLastSessionStatus()
        {
            return _lastSessionStatus.ToString();
        }
        public string Progress()
        {
            return _progress.ToString("P0");
        }

        // commands from UI
        public void Cancel()
        {
            SendMessage(string.Format("{0} Canceling...", DateTime.Now.ToString("HH:mm:ss")));
            if (_tokenSource != null)
                _tokenSource.Cancel();
        }

        public void Start(List<SyncStepDescription> steps)
        {

            if (_isSynchronizing)
            {
                SendMessage("Service is in the process of synchronization, you cant stop it.");
                return;
            }

            _sortedUserConfiguredSteps = steps.Where(i => i.Execute).OrderBy(k => k.ExecutionOrder).ToList();

            StartSync(false);
        }

        private static string InsertCheckPoint(string name)
        {
            if (SettingsHelper.Read<bool>("WaitReplicaConfirmation", false) && !string.IsNullOrEmpty(_gaMongoDbConnectionString))
            {
                string dburl = _gaMongoDbConnectionString.EndsWith("/") ? _gaMongoDbConnectionString + "ClientDWMaster" : _gaMongoDbConnectionString + "/ClientDWMaster";

                var miliseconds2Wait = SettingsHelper.Read<int>("WaitReplicaConfirmationTimeOutMs", 5 * 60 * 1000);
                var repository = new RepositoryCollection<CheckPoint>(dburl, 2, TimeSpan.FromMilliseconds(miliseconds2Wait));
                var watch = new Stopwatch();
                repository.Create(new CheckPoint() { Name = name, DateTime = DateTime.Now, Id = Guid.NewGuid() });
                watch.Stop();

                return string.Format("After [{0}], confirmed update duration {1} ms.", name, watch.ElapsedMilliseconds);
            }

            return string.Empty;
        }

        private static void PrepareDatabases(ProductType productType)
        {
            if (SettingsHelper.Read("SkipClonning", false))
                return;

            CloneDatabase(productType, ConnectionStringType.ScreeningDW);
            _tokenSource.Token.ThrowIfCancellationRequested();

            if (productType == ProductType.GA)
            {
                CloneDatabase(productType, ConnectionStringType.ClientDW);
                _tokenSource.Token.ThrowIfCancellationRequested();

                CloneDatabase(productType, ConnectionStringType.ClientUniverses);
                _tokenSource.Token.ThrowIfCancellationRequested();

                CloneDatabase(productType, ConnectionStringType.GovernanceDW);
                _tokenSource.Token.ThrowIfCancellationRequested();

                CloneDatabase(productType, ConnectionStringType.DWArchive);
                _tokenSource.Token.ThrowIfCancellationRequested();
            }

            if (productType == ProductType.GA)
                TestDb();
        }


        private static void CancelPdfGeneration()
        {
            try
            {
                PDFGeneratorHttpClient client =
                    new PDFGeneratorHttpClient(
                        SettingsHelper.Read("pdfurlcancelation", "http://localhost/pdfgenerationserv/"));
                client.CancelGenerationAsync().Wait();
                WriteInListAndHub("Cancelling pdf generation done");
            }
            catch (AggregateException)
            {
                WriteInListAndHub("PdfGeneration sevice not available");
            }
        }

        private static void TestDb()
        {
            for (var i = 36; i > 0; i--)
            {
                try
                {
                    System.Threading.Thread.Sleep(5 * 1000); // 5 secs pause after clone

                    var msg = string.Format("Testing db trial {0}", i);
                    _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, msg));
                    SendMessage(msg);

                    var testRep = new RepositoryCollection<Test>(_gaConnectionStrings["ClientDW"]);
                    testRep.Create(new Test { Id = Guid.NewGuid(), Text = "text", Number = 100 });
                    testRep.Drop();

                    i = 0;
                }
                catch (Exception ex)
                {
                    _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Log, ex.ToString()));
                }
            }
        }

        class Test : IEntity
        {
            public Guid Id { get; set; }
            public string Text { get; set; }
            public int Number { get; set; }
        }

        private static void CloneDatabase(ProductType productType, ConnectionStringType connectionStringType)
        {
            var mongoDbConnectionString = _gaConnectionStrings["ClientPlatform"];
            if (productType == ProductType.DSO)
                mongoDbConnectionString = _dsoConnectionStrings["ClientPlatform"];

            var fromDbName = string.Format("{0}_{1}", connectionStringType, _lastDatabaseGroup);
            var toDbName = string.Format("{0}_{1}", connectionStringType, _currentDatabaseGroup);


            var msg = "Start clonning " + fromDbName;
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Log, msg));
            SendMessage(msg);
            var sw = Stopwatch.StartNew();

            var client = new MongoClient(mongoDbConnectionString);
            var database = client.GetDatabase(toDbName);
            var adminDatabase = client.GetDatabase("admin");
            var okKey = "ok";

            var dropResult = database.RunCommand(new BsonDocumentCommand<BsonDocument>(new BsonDocument() { new BsonElement("dropDatabase", 1 )}));
            if (!dropResult.Contains(okKey) || !dropResult[okKey].IsDouble || dropResult[okKey].AsDouble != 1)
                throw new BsonException(string.Format("Database {0} can't be deleted!", toDbName));

            var copyDbCommand = new BsonDocumentCommand<BsonDocument>(new BsonDocument()
            {
                new BsonElement("copydb", 1),
                new BsonElement("fromdb", fromDbName),
                new BsonElement("todb", toDbName)
            });

            var copyDbResult = adminDatabase.RunCommand(copyDbCommand);

            if (!copyDbResult.Contains(okKey) || !copyDbResult[okKey].IsDouble || copyDbResult[okKey].AsDouble != 1)
                throw new BsonException(string.Format("{0} database can't be copied!", fromDbName));

            sw.Stop();

            var message = string.Format(@"{0} database was cloned as {1}. Time elapsed {2:hh\:mm\:ss}",
                fromDbName, toDbName, sw.Elapsed);


            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Log, message));

            SendMessage(message);
        }

        private static bool HasStaleData(string sqlConnectionString, DateTime previousSyncDate, out string database)
        {
            using (var sqlConnection = new SqlConnection(sqlConnectionString))
            {
                database = sqlConnection.Database;

                sqlConnection.ConnectionString = sqlConnection.ConnectionString.Replace(database, "msdb");

                sqlConnection.Open();

                var sqlCommand = new SqlCommand
                {
                    Connection = sqlConnection,
                    CommandType = CommandType.Text,
                    CommandText = string.Format("select max(restore_date) from msdb.dbo.restorehistory where destination_database_name = '{0}'", database)
                };

                var lastDatabaseRestoreDate = sqlCommand.ExecuteScalar() as DateTime?;// null in case sqlConnection.Database was never restored on that server

                sqlCommand.CommandText = string.Format("select databasepropertyex('{0}', 'Status')", database);

                var databaseStatus = sqlCommand.ExecuteScalar() as string;

                var sourceDatabase = database.Replace("_BACKUP", string.Empty);

                sqlCommand.CommandText = string.Format("select count(*) from sys.dm_exec_requests where database_id = db_id('{0}') and command = 'BACKUP DATABASE'", sourceDatabase);

                var isSourceDbBackupInProgres = (int)sqlCommand.ExecuteScalar() > 0;

                var infoMessage = $"Status data collected for database {database} " +
                                  $"(previous sync date - {previousSyncDate:G}): last restore date - " +
                                  $"{lastDatabaseRestoreDate:G}, current status - {databaseStatus}, " +
                                  $"source database backup in progress - {(isSourceDbBackupInProgres ? "YES" : "NO")}.";

                SendMessage(infoMessage);
                ClientDWEventSource.Log.Message(infoMessage);
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, string.Format("<font color='0000FF'>{0:HH:mm:ss} {1}</font>", DateTime.Now, infoMessage)));

                return !(lastDatabaseRestoreDate > previousSyncDate && databaseStatus == "ONLINE" && !isSourceDbBackupInProgres);
            }
        }

        private static void EnsureSqlDataSourcesFreshness(DateTime previousSyncDate)
        {
            var checkAttempts = 0;
            var sqlFreshDataCheckAttemptsThreshold = ConfigurationManager.Instance.AppSettings.AppSettingSilent("SqlDataSourcesFreshnessCheckAttempts", () => 7);
            var sqlFreshDataCheckInterval = ConfigurationManager.Instance.AppSettings.AppSettingSilent("SqlDataSourcesFreshnessCheckInterval", () => 10);

            do
            {
                string database;
                if (HasStaleData(_gaConnectionStrings[RpConnectionType], previousSyncDate, out database))
                {
                    WaitForDatabaseRefresh(database, sqlFreshDataCheckInterval);

                    continue;
                }

                if (HasStaleData(_gaConnectionStrings[EventsConnectionType], previousSyncDate, out database))
                {
                    WaitForDatabaseRefresh(database, sqlFreshDataCheckInterval);

                    continue;
                }

                if (HasStaleData(_gaConnectionStrings[GccsConnectionType], previousSyncDate, out database))
                {
                    WaitForDatabaseRefresh(database, sqlFreshDataCheckInterval);

                    continue;
                }

                break;// on fresh data
            } while (++checkAttempts < sqlFreshDataCheckAttemptsThreshold);

            if (checkAttempts == sqlFreshDataCheckAttemptsThreshold)
            {
                throw new ApplicationException(string.Format("The SQL data sources were not refreshed since {0:G} when the previous sync took place! Quiting waiting", previousSyncDate));
            }
        }

        private static void WaitForDatabaseRefresh(string database, int minutesToWait)
        {
            var infoMessage = string.Format("Start waiting for {0} minutes hoping that meanwhile the database {1} would refresh", minutesToWait, database);

            SendMessage(infoMessage);
            ClientDWEventSource.Log.Message(infoMessage);
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, string.Format("<font color='0000FF'>{0:HH:mm:ss} {1}</font>", DateTime.Now, infoMessage)));

            Task.Delay(TimeSpan.FromMinutes(minutesToWait)).Wait(_tokenSource.Token);// wait for a while hoping that the sql data source would refresh
        }

        private static void StartSync(bool autoInitiated)
        {
            _sessionAutoStarted = autoInitiated;
            lock (SyncLock)
            {
                if (_isSynchronizing)
                    return;

                _isSynchronizing = true;
                SyncHub.HubContext.Clients.All.isSyncChange(_isSynchronizing);

                _lastSession = new SyncronizationSessionDTO { StartDate = DateTime.UtcNow, Status = "Starting" };
            }

            SendMessage(string.Format("{0} Synchronizing...", DateTime.Now.ToString("HH:mm:ss")));

            _tokenSource = new CancellationTokenSource();

            var task = new Task(SyncAction, _tokenSource.Token);
            task.ContinueWith(SyncCanceledAction, TaskContinuationOptions.OnlyOnCanceled);
            task.ContinueWith(SyncFaultedAction, TaskContinuationOptions.OnlyOnFaulted);
            task.ContinueWith(OnCompletion, TaskContinuationOptions.OnlyOnRanToCompletion);

            task.Start();
        }

        private static void OnCompletion(Task task)
        {
            OnSyncEnded(LastSessionStatus.Finished);
        }

        private static void SyncFaultedAction(Task task)
        {
            OnSyncEnded(LastSessionStatus.Failed);


            var exceptions = new StringBuilder();
            var ae = task.Exception;
            if (ae != null)
            {
                foreach (var inner in ae.InnerExceptions)
                    exceptions.AppendFormat("{0} StackTrace: {1}", inner.Message, inner.StackTrace);
            }

            SendMessage(exceptions.ToString());
            ClientDWEventSource.Log.Error(exceptions.ToString());
            _allMessages.Add(
                new Tuple<SyncMessageType, string>(
                    SyncMessageType.EmailAndLog,
                    $"<b><font color='FF0000'>Failed to synchronize Client DW.</b><p><font color='FF0000'>Exceptions details: {exceptions}</p>"));
        }

        private static void SyncCanceledAction(Task task)
        {
            OnSyncEnded(LastSessionStatus.Canceled);
        }

        private static void SyncAction()
        {
            var swTimeSync = Stopwatch.StartNew();
            StatusChange(LastSessionStatus.Running);
            ProgressChange(0.00);

            _lastSyncTime = null;
            _lastDatabaseGroup = LoadLastDatabaseGroup();
            _syncLog = _clientDwMasterRepository.AddNewEntryInSyncLog(_sessionAutoStarted);

            _allMessages.Clear();
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                string.Format("Session is initiated on timer: {0}", _sessionAutoStarted)));
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                string.Format("Recurring time is: {0}", _initialRecurringDate.ToString("G"))));
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                string.Format("The processor count used in this process is: {0}",
                    ParallelHelper.GetMaxDegreeOfParallelism())));


            if (_sortedUserConfiguredSteps != null && _sortedUserConfiguredSteps.Any())
            {
                var agg = _sortedUserConfiguredSteps
                                    .Select(a => a.Name)
                                    .Aggregate((a, b) => string.Format("{0}{2}{1}", a, b, Environment.NewLine));
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                    string.Format("The following steps will be executed during this synchronization:{0}", agg)));
            }

            UpdateCurrentDatabaseGroup(); // switches database groups current becomes different ffom last

            SyncHub.HubContext.Clients.All.databaseGroup(_currentDatabaseGroup.ToString());
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                string.Format("Synchronization using error policy:{0}", _errorPolicy)));
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                string.Format("Syncronization writes to  databaseGroup {0}", _currentDatabaseGroup)));

            var syncDsoDatabase = SettingsHelper.Read("SyncDsoDatabase", false);
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                string.Format("Sync DSO Database:{0}", syncDsoDatabase)));

            SetGAConnectionStrings();
            if (syncDsoDatabase)
            {
                SetDSOConnectionStrings();
            }

            var useBackups = SettingsHelper.Read<bool>("UseBackups", false);

            if (useBackups)
            {
                var sw = Stopwatch.StartNew();

                var startMsg = string.Format("Checking for SQL Backups is beginning at {0}",
                    DateTime.Now.ToString("HH:mm:ss"));
                SendMessage(startMsg);
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, startMsg));


                var lastAutomatedSyncLog = _clientDwMasterRepository.GetLastSync(true);
                if (_sessionAutoStarted && lastAutomatedSyncLog != null && lastAutomatedSyncLog.TimeEnd != null)
                {
                    _serviceStatus = SyncServiceStatus.CheckingForSQLBackups;
                    EnsureSqlDataSourcesFreshness(lastAutomatedSyncLog.TimeEnd.Value);
                }
                var endMsg = string.Format(@"Checking for SQL Backups finished at {0}. Time elapsed {1:hh\:mm\:ss}",
                                        DateTime.Now.ToString("G"), sw.Elapsed);
                SendMessage(endMsg);
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, endMsg));

            }
            else
            {
                var startMsg = "Skiping sql backups check";
                SendMessage(startMsg);
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, startMsg));
            }


            CancelPdfGeneration();

            PrepareDatabases(ProductType.GA);
            if (syncDsoDatabase)
            {
                PrepareDatabases(ProductType.DSO);
            }

            _serviceStatus = SyncServiceStatus.ExecutingSteps;
            InsertCheckPoint("ExecutingSteps");

            _syncContext = new SyncContext(_errorPolicy, SendMessage)
            {
                GAConnectionStrings = _gaConnectionStrings,
                LastGAConnectionStrings = GetCurrentConnectionStrings(),
                DSOConnectionStrings = _dsoConnectionStrings,
                PreviousChanges = new List<ChangedElement>(),
                LastSyncTime = _lastSyncTime,
                IsInitialSync = _isInitialSync,
                Token = _tokenSource.Token,
                Report = new ConcurrentBag<string>(),
            };

            _result = SyncOperationResult.Fail;


            var syncSteps = LoadSyncSteps();
            syncSteps = ReorderStepsByConfigOrUserOrder(syncSteps);
            _syncContext.SelectedSteps = syncSteps.Select(x => x.ExecutionOrder).ToList();
            _result = RunSyncSteps(syncSteps);
            swTimeSync.Stop();

            PublishComputationSet(_result, _syncContext);
            if (_result == SyncOperationResult.Done)
            {

                WriteInListAndHub(string.Format("The synchronization result is {0}, proceeding to updating connection strings",
                    _result));

                TryUpdateConnectionStringsInClientPlatform(syncDsoDatabase);

                //sending sync message to link: CartePath"
                var carteConnection = new SyncFinished("Send sync message", _cartePath, _carteMessage,"");
                carteConnection.SendSyncFinishedMessage();
            }

            _syncLog.TimeSync = swTimeSync.Elapsed.ToString();
            var msgFinal = string.Format(@"{2}{3} Database group: {0}. Total time elapsed {1:hh\:mm\:ss}",
                _currentDatabaseGroup, swTimeSync.Elapsed, GetSubjectBySynOpResult(_result), _result == SyncOperationResult.Fail ? string.Empty : ".");

            SendMessage(msgFinal);
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Email, msgFinal));


            ClientDWEventSource.Log.Message(msgFinal);

            _syncContext.ResetCurrentChanges();
            _syncContext = null;

            GC.Collect();
        }

        private static void PublishSynchronizationFinishedEvent()
        {
            var settings = new ActiveMQAppSettings(ConfigurationManager.Instance.AppSettings, ConfigurationManager.Instance.ConnectionStrings);
            using (ITopicPublisher pub = new ActiveMQTopicPublisher(settings))
            {
                pub.SendMessage(TopicEvents.SynchronizationFinished.ToString());
            }
        }

        private static Guid? GetSyncComputationSetId(SyncContext syncContext)
        {
            var syncContextInfo = syncContext.PreviousChanges.FirstOrDefault(i => i.AffectedObjectName == ChangedElement.DATA_SET_DELETE_SYNC_FAIL);
            return syncContextInfo?.AffectedDocumentId;
        }


        private static async Task<ComputationSetMessage> GetLastClientSetId(SyncOperationResult syncstatus, SyncContext syncContext)
        {
            using (var esgClient = new ESGClient(syncContext.GAConnectionStrings["ComputationServiceAddress"]))
            {
                var allSets = await esgClient.GetSetIds();
                var activeSet = await esgClient.GetLatestActiveSet();
                var syncSet = GetSyncComputationSetId(syncContext);

                var setInfo = syncstatus == SyncOperationResult.Done ?
                    new ComputationSetMessage() { ActiveSetId = syncSet, DeletableSetIds = allSets.Except(new[] { syncSet.GetValueOrDefault() }).ToArray() } :
                    new ComputationSetMessage() { ActiveSetId = activeSet, DeletableSetIds = allSets.Except(new[] { activeSet }).ToArray() };

                return setInfo;
            }
        }

        private static void PublishComputationSet(SyncOperationResult syncstatus, SyncContext synccontext)
        {
            var setInfo = GetLastClientSetId(syncstatus, synccontext).Result;

            synccontext.Log($"Updating to set with {setInfo.ActiveSetId} and deleting sets {string.Join(",", setInfo.DeletableSetIds)}");

            var settings = new ActiveMQAppSettings(ConfigurationManager.Instance.AppSettings, ConfigurationManager.Instance.ConnectionStrings);

            using (var factory = new ActiveMQFactory(settings))
            {
                using (var pub = factory.CreateProducer())
                {
                    pub.SendMessage(setInfo);
                }
            }
        }

        private static void WriteInListAndHub(string msg)
        {
            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, msg));
            SendMessage(msg);
        }

        private static void OnSyncEnded(LastSessionStatus syncStatus)
        {
            try
            {
                _serviceStatus = SyncServiceStatus.Dormant;
                StatusChange(syncStatus);
                ProgressChange(1.00);

                _syncLog.Status = _lastSessionStatus;
                _syncLog.DatabaseGroup = _currentDatabaseGroup;
                _syncLog.TimeSync = _syncLog.TimeSync ?? string.Empty;
                _clientDwMasterRepository.UpdateSyncLog(_syncLog);


                UpdateAutomaticRunDateTime();

                PublishSynchronizationFinishedEvent();
                SendMessage($"{TopicEvents.SynchronizationFinished} event was sent to ActiveMQ Topic");
            }
            catch (Exception e)
            {
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Log,
                    $"<b><font color='FF0000'>Exception: {e.ToString()}</b>"));

                SendMessage($"Exception: {e.ToString()}");
            }
            finally
            {
                var msgFinal =
                    $@"Syncronization ended, result: {GetSubjectBySynOpResult(_result)},  Last session status: {
                        _lastSessionStatus}, Current database group: {_currentDatabaseGroup}.";

                SendMessage(msgFinal);
                ClientDWEventSource.Log.Message(msgFinal);
                string subj = CreateEmailSubject();
                NotifySupportChannel(subj);
                SendWeightMatricesAlert();

                lock (SyncLock)
                {
                    _isSynchronizing = false;
                    SyncHub.HubContext.Clients.All.isSyncChange(_isSynchronizing);
                }

                if (_result == SyncOperationResult.Done && syncStatus == LastSessionStatus.Finished && !Environment.UserInteractive)
                {
                    ///kill the current process in case it is a Windows service;
                    ///this action here assumes there is a recovery policy set on the Windows service to restart it automatically upon abnormal termination
                    Environment.Exit(1);
                }
            }
        }

        private static string CreateEmailSubject()
        {

            var deploymentLocation = ConfigurationManager.Instance.AppSettings["DeploymentLocation"];
            var subjStart = SettingsHelper.Read("SynchronizationName", "GA publishing");
            var machineName = deploymentLocation == DeploymentLocation.Development.ToString() ? Environment.MachineName.ToUpperInvariant() : deploymentLocation;
            var resulstr = string.Empty;
            if (_result == SyncOperationResult.Done)
            {
                resulstr = "Successful";
            }
            if (_result == SyncOperationResult.Canceled)
            {
                resulstr = "Canceled";
            }
            if (_result == SyncOperationResult.Fail)
            {
                resulstr = "Failed";
            }
            if (_result == SyncOperationResult.DoneWithErrors)
            {
                resulstr = "Failed";
            }

            var subj = string.Format("{0} {1} {2}", subjStart, machineName, resulstr);
            return subj;

        }

        private static void UpdateCurrentDatabaseGroup()
        {
            switch (_lastDatabaseGroup)
            {
                case DatabaseGroup.A:
                    _currentDatabaseGroup = DatabaseGroup.B;
                    break;
                default:
                case DatabaseGroup.B:
                    _currentDatabaseGroup = DatabaseGroup.A;
                    break;
            }
        }

        private static void UpdateAutomaticRunDateTime()
        {
            while (_initialRecurringDate <= DateTime.UtcNow)
            {
                _initialRecurringDate = _initialRecurringDate.Add(_regularRepeatInterval);
            }

            if (_allMessages != null)
            {
                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                    string.Format("<b>DailyRecurringDate Changed: {0}</b>",
                        _initialRecurringDate.ToString(CultureInfo.InvariantCulture))));
                SendMessage(string.Format("InitialRecurringDate Changed: {0}",
                    _initialRecurringDate.ToString(CultureInfo.InvariantCulture)));
            }
        }

        private static string GetSubjectBySynOpResult(SyncOperationResult result)
        {
            var subject = string.Empty;
            switch (result)
            {
                case SyncOperationResult.Done:
                    subject = "completed successfully";
                    break;
                case SyncOperationResult.DoneWithErrors:
                    subject = "completed successfully, but with errors";
                    break;
                case SyncOperationResult.Fail:
                    subject = "Failed !";
                    break;
                case SyncOperationResult.Canceled:
                    subject = "Canceled";
                    break;
            }
            return subject;
        }



        private static List<ISyncStep> LoadSyncSteps()
        {
            try
            {
                var syncSteps = new List<ISyncStep>();
                foreach (SyncStepOrder executionOrder in Enum.GetValues(typeof(SyncStepOrder)))
                {
                    StepDictionary.TryGetValue(executionOrder, out ISyncStep syncStep);

                    if (syncStep == null)
                        throw new NotImplementedException($"For the current value:{executionOrder} there is no implementation");

                    syncSteps.Add(syncStep);
                }

                return syncSteps;
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry("Sustainalytics.Synchronization.Service", ex.ToString(), EventLogEntryType.Error);
                Console.WriteLine(ex.ToString());
                throw;
            }
        }

        private static List<ISyncStep> ReorderStepsByConfigOrUserOrder(List<ISyncStep> syncSteps)
        {
            List<SyncStepDescription> localSteps = null;
            lock (SyncLock)
            {
                if (_sortedUserConfiguredSteps != null)
                    localSteps = _sortedUserConfiguredSteps;
            }

            if (localSteps != null)
            {
                var result = new List<ISyncStep>();
                foreach (var step in localSteps)
                    result.Add(syncSteps.SingleOrDefault(x => x.ExecutionOrder == step.Id));
                return result;
            }

            var key = ConfigurationManager.Instance.AppSettings["Steps"];

            if (!string.IsNullOrEmpty(key))
            {
                var orderList = new List<SyncStepOrder>();
                var result = new List<ISyncStep>();
                var steps = key.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var step in steps)
                {
                    SyncStepOrder customOrderStep;
                    if (Enum.TryParse<SyncStepOrder>(step, true, out customOrderStep))
                        orderList.Add(customOrderStep);
                }

                foreach (var id in orderList.Distinct())
                    result.Add(syncSteps.SingleOrDefault(x => x.ExecutionOrder == id));

                if (result.Count > 0)
                    return result;
            }

            return syncSteps.ToList();
        }

        private static SyncOperationResult RunSyncSteps(List<ISyncStep> syncSteps)
        {
            var swTotal = Stopwatch.StartNew();

            var resultSyncOp = SyncOperationResult.Done;


            SetControversyEsgProfile(_syncContext);

            LogConnectionStrings(_syncContext);


            var sbError = new StringBuilder("");

            var sb = new StringBuilder();
            foreach (var step in syncSteps)
            {
                sb.Append(string.Format("{0} ", step.ExecutionOrder));
            }

            SendMessage(string.Format("Executing steps: {0} ", sb.ToString()));

            var strAuxMsg = string.Empty;
            var endMsg = string.Empty;
            var beginMsg = string.Empty;
            ISyncStep prevErrorInitStep = null;

            var outputSynResult = syncSteps.ToDictionary(a => a, a => resultSyncOp);

            try
            {
                double stepCount = syncSteps.Count;
                double index = 1;
                foreach (var syncStep in syncSteps)
                {
                    try
                    {
                        // check for task cancellation
                        _tokenSource.Token.ThrowIfCancellationRequested();
                        beginMsg = string.Format(@"<b><font color='4169E1'>Step {0} - {1} is beginning at {2}",
                            (int)syncStep.ExecutionOrder, syncStep.ExecutionOrder, DateTime.Now.ToString("G"));
                        _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, beginMsg));
                        strAuxMsg = string.Format("Step {0} - {1} running...", (int)syncStep.ExecutionOrder, syncStep.ExecutionOrder);
                        SendMessage(strAuxMsg);
                        var stepTime = Stopwatch.StartNew();

                        var syncStepExecutionAttempts = 0;

                        do
                        {
                            try
                            {
                                Thread.Sleep(TimeSpan.FromSeconds(_SyncStepRetryInitialInterval * syncStepExecutionAttempts));

                                syncStepExecutionAttempts++;// count the current attempt

                                _syncContext.Status = SyncOperationStatus.Succeeded;

                                _syncContext = syncStep.Start(_syncContext);
                            }
                            catch (Exception syncStepException)
                            {
                                if (syncStepException is OperationCanceledException || syncStepExecutionAttempts == _MaxSyncStepExecutionAttempts)
                                {
                                    throw;// rethrow exception along with its stack trace
                                }

                                _syncContext.Status = SyncOperationStatus.Failed;
                            }
                        } while (_syncContext.Status != SyncOperationStatus.Succeeded && syncStepExecutionAttempts < _MaxSyncStepExecutionAttempts);

                        var tolog = InsertCheckPoint(syncStep.ExecutionOrder.ToString());
                        _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Log, tolog));

                        if (_syncContext.Status == SyncOperationStatus.Failed)
                        {
                            outputSynResult[syncStep] = SyncOperationResult.Fail;
                            sbError.Append(syncStep.GetType().Name);
                        }

                        var changes = _syncContext.PreviousChanges.ToList();
                        changes.AddRange(_syncContext.CurrentChanges);
                        _syncContext.PreviousChanges = changes;
                        _syncContext.ResetCurrentChanges();

                        _allMessages.AddRange(_syncContext.Report.Select(a => new Tuple<SyncMessageType, string>(SyncMessageType.Log, a)));

                        if (!_syncContext.LoggingEnabled)
                        {
                            //no need to SendMessage again if we already do SendMessage inside _syncContext.Log
                            foreach (var text in _syncContext.Report.ToList())
                                SendMessage(string.Format("\t {0}", text));
                        }

                        _syncContext.Report = new ConcurrentBag<string>();

                        _progress = index / stepCount;
                        ProgressChange(_progress);

                        stepTime.Stop();
                        endMsg = string.Format(@"Step {0} - {1} finished at {3}. Time elapsed {2:hh\:mm\:ss}",
                            (int)syncStep.ExecutionOrder, syncStep.ExecutionOrder, stepTime.Elapsed,
                            DateTime.Now.ToString("G"));
                        SendMessage(endMsg);
                        index++;



                        _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog,
                            string.Format(@"<b><font color='4169E1'>{0}</font></b>", endMsg)));
                    }
                    catch (OperationCanceledException)
                    {
                        outputSynResult[syncStep] = SyncOperationResult.Canceled;
                        throw;
                    }
                    catch (Exception ex)
                    {
                        outputSynResult[syncStep] = SyncOperationResult.Fail;

                        if (prevErrorInitStep != null)
                            sbError.Append(",");
                        var errorMsg = string.Format("{2} The following error at step:{0} appeared:{1}",
                            syncStep.GetType().Name, ex, DateTime.Now.ToString("G"));
                        SendMessage(errorMsg);
                        _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, string.Format("<b><font color='FF0000'>{0}</font></b>", errorMsg)));
                        sbError.Append(syncStep.GetType().Name);
                        prevErrorInitStep = syncStep;
                        //handle deletion historical dataset
                        var historicalDeletion =
                            _syncContext.CurrentChanges.FirstOrDefault(
                                a => a.AffectedObjectName == ChangedElement.DATA_SET_DELETE_SYNC_FAIL);
                        if (historicalDeletion == null)
                        {
                            historicalDeletion =
                            _syncContext.PreviousChanges.FirstOrDefault(
                                a => a.AffectedObjectName == ChangedElement.DATA_SET_DELETE_SYNC_FAIL);
                        }
                        else
                        {
                            var outpupt = (Func<bool>)historicalDeletion.Detail[ChangedElement.DATA_SET_DELETE_SYNC_FAIL];
                            if (!outpupt())
                            {
                                string message = $"The deletion of the dataset {(object)historicalDeletion.AffectedDocumentId} built in this sync has failed";
                                SendMessage(message);

                                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, message));
                            }
                        }

                        var changes = _syncContext.PreviousChanges.ToList();
                        changes.AddRange(_syncContext.CurrentChanges);
                        changes.Add(new ChangedElement()
                        {
                            AffectedDocumentType = typeof(ISyncStep),
                            AffectedObjectName = ChangedElement.AFFECTED_SYNC_RESULT_NAME_OBJECT
                        });
                        _syncContext.PreviousChanges = changes;


                        if (_errorPolicy == ErrorPolicy.BreakRightAway)
                            throw;
                    }
                }
            }
            finally
            {
                swTotal.Stop();
                string resultMsg;

                resultSyncOp = GetSyncOperationResult(outputSynResult, resultSyncOp, swTotal, sbError, out resultMsg);
                _result = resultSyncOp;
                Console.WriteLine(resultMsg);

                _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.EmailAndLog, resultMsg));
            }

            return resultSyncOp;
        }

        private static SyncOperationResult GetSyncOperationResult(Dictionary<ISyncStep, SyncOperationResult> outputSynResult, SyncOperationResult resultSyncOp,
            Stopwatch swTotal, StringBuilder sbError, out string resultMsg)
        {
            resultMsg = string.Empty;
            if (outputSynResult.Any(a => a.Value == SyncOperationResult.Canceled))
            {
                resultSyncOp = SyncOperationResult.Canceled;
                resultMsg =
                    string.Format(
                        "Syncronization canceled at the step {1}. Total time elapsed {0:#0.00} min",
                        swTotal.Elapsed.TotalMinutes, outputSynResult.FirstOrDefault(a => a.Value == SyncOperationResult.Canceled).Key.ExecutionOrder);
                return resultSyncOp;
            }
            if (outputSynResult.All(a => a.Value == SyncOperationResult.Fail)) //all failed
            {
                resultSyncOp = SyncOperationResult.Fail;
                resultMsg = String.Format(
                        "Syncronization failed. All the selected steps failed. Total time elapsed {0:#0.00} min",
                        swTotal.Elapsed.TotalMinutes);
            }

            if (outputSynResult.Any(a => a.Value == SyncOperationResult.Done) &&
                outputSynResult.Any(a => a.Value == SyncOperationResult.Fail)) //done and some with errors
            {
                resultSyncOp = SyncOperationResult.DoneWithErrors;
                resultMsg = String.Format(
                        "Syncronization completed, but there were some errors on steps:{1} please check the html log for more detailed information. Total time elapsed {0:#0.00} min",
                        swTotal.Elapsed.TotalMinutes, sbError);
            }

            if (outputSynResult.All(a => a.Value == SyncOperationResult.Done)) //all were succesful
            {
                resultSyncOp = SyncOperationResult.Done;
                resultMsg = string.Format("Synchronization completed successfully.Total time elapsed {0:#0.00} min",
                    swTotal.Elapsed.TotalMinutes);
            }
            return resultSyncOp;
        }

        private static void SetControversyEsgProfile(SyncContext syncContext)
        {
            var changedElement = new ChangedElement() { AffectedObjectName = ChangedElement.ESG_CONTROVERSY_PROFILE };
            var esgProfile = new ESGProfile();
            var insert = false;

            if (!string.IsNullOrEmpty(ConfigurationManager.Instance.AppSettings["ReferenceUniverseId"]))
            {
                esgProfile.ReferenceUniverseId = Guid.Parse(ConfigurationManager.Instance.AppSettings["ReferenceUniverseId"]);
                insert = true;
            }

            if (!string.IsNullOrEmpty(ConfigurationManager.Instance.AppSettings["ComparativeGroup"]))
            {
                ComparativeGroupEnum compgroup;
                ComparativeGroupEnum.TryParse(ConfigurationManager.Instance.AppSettings["ComparativeGroup"],
                    out compgroup);
                esgProfile.ComparativeGroup = compgroup;
                insert = true;
            }
            if (!string.IsNullOrEmpty(ConfigurationManager.Instance.AppSettings["CompanyType"]))
            {
                TemplateTypeEnum templateTypeParse;
                TemplateTypeEnum.TryParse(ConfigurationManager.Instance.AppSettings["CompanyType"], out templateTypeParse);
                esgProfile.TemplateType = templateTypeParse;
                insert = true;
            }

            changedElement.Detail = new Dictionary<string, object>();
            if (insert) changedElement.Detail.Add("ESGProfile", esgProfile);
            syncContext.AddChange(changedElement);
        }

        private static void LogConnectionStrings(SyncContext syncContext)
        {
            StringBuilder message = new StringBuilder();
            foreach (var key in syncContext.GAConnectionStrings.Keys)
                message.Append(string.Format("[{0}={1}]\n", key, syncContext.GAConnectionStrings[key]));

            _allMessages.Add(new Tuple<SyncMessageType, string>(SyncMessageType.Log, message.ToString()));
            SendMessage(message.ToString());
        }



        private static void SendMessage(string message)
        {
            lock (SyncLock)
            {
                if (_lastSession != null)
                    _lastSession.Messages.Add(message);
            }
            SyncHub.HubContext.Clients.All.addMessage(string.Format("{0} {1}", DateTime.Now.ToString("HH:mm:ss"), message));
        }

        private static void StatusChange(LastSessionStatus newstate)
        {
            _lastSessionStatus = newstate;
            lock (SyncLock)
            {
                if (_lastSession != null)
                    _lastSession.Status = _lastSessionStatus.ToString();
            }


            SyncHub.HubContext.Clients.All.statusChange(_lastSession.Status);
        }

        private static void ProgressChange(double progress)
        {
            _progress = progress;
            var messageProgress = _progress.ToString("P0");
            lock (SyncLock)
            {
                if (_lastSession != null)
                    _lastSession.Progress = messageProgress;
            }

            SyncHub.HubContext.Clients.All.progressChange(messageProgress);
        }

        private static void SendWeightMatricesAlert()
        {
            if (_syncContext != null)
            {
                var changes = _syncContext.PreviousChanges
                    .Where(x => x.AffectedObjectName == ChangedElement.AFFECTED_INPUT_FILE_NAME_OBJECT)
                    .ToList();

                var failures = changes.Where(x => x.Status == SyncResultStatus.AppliedFailure).ToList();
                if (failures.Any())
                {
                    const string subject = "Weight matrices failures";
                    var body = new StringBuilder();

                    foreach (var failure in failures)
                    {
                        var document = (EsgInputFileComplete)failure.AffectedDocument;

                        body.AppendFormat("<p><b>Weight matrix: {0}</b></p>", document.Name);
                        body.AppendFormat("<p>File name: {0}</b></p>", document.FileName);
                        if (failure.Detail.Any(a => a.Key.Equals("ErrorsLog")))
                        {
                            var errorsLog = failure.Detail["ErrorsLog"];
                            if (errorsLog != null)
                            {
                                body.AppendFormat("<p><font color='FF0000'>Errors: {0}</font></p>", errorsLog);
                            }
                        }
                    }

                    SendAlert(subject, body.ToString());
                }
            }
        }

        private static void SendAlert(string subject, string body)
        {
            try
            {
                var deploymentLocation = ConfigurationManager.Instance.AppSettings["DeploymentLocation"];
                var supportEmail = ConfigurationManager.Instance.AppSettings["SupportEmail"];

                subject = deploymentLocation == DeploymentLocation.Development.ToString() ?
                    subject + " - " + Environment.MachineName.ToUpperInvariant() :
                    subject + " - " + deploymentLocation;

                SendEmail(new[] { supportEmail }, null, subject, body, true, false, string.Empty, new byte[0]);
            }
            catch (Exception e)
            {
                ClientDWEventSource.Log.Error(
                    string.Format("{0} - Failed to send notifications onto the support channel! {1}{2}",
                        DateTime.Now, Environment.NewLine, e));
            }
        }

        private static void NotifySupportChannel(string subject)
        {
            if (!_allMessages.Any()) return;

            try
            {
                var sb = new StringBuilder();

                sb.AppendLine("<!DOCTYPE html>");
                sb.AppendLine("<html>");
                sb.AppendLine("<head>");
                sb.AppendLine("<title>Sync log</title>");
                sb.AppendLine("</head>");
                sb.AppendLine("<body>");
                foreach (var report in _allMessages.Where(a => a.Item1 != SyncMessageType.Email))
                {
                    sb.AppendFormat("<p>{0}</p>{1}", report.Item2, Environment.NewLine);
                }
                sb.AppendLine("</body>");
                sb.AppendLine("</html>");



                var sbImp = new StringBuilder();

                foreach (var r in _allMessages.Where(a => a.Item1 != SyncMessageType.Log))
                {
                    sbImp.AppendFormat("<p>{0}</p>", r.Item2);
                }


                var body = sbImp.ToString();


                var supportEmail = ConfigurationManager.Instance.AppSettings["SupportEmail"];

                SendEmail(new[] { supportEmail }, null, subject, body, true, true, "sync.html", Encoding.UTF8.GetBytes(sb.ToString()));
            }
            catch (Exception e)
            {
                ClientDWEventSource.Log.Error(
                    string.Format("{0} - Failed to send notifications onto the support channel! {1}{2}",
                        DateTime.Now, Environment.NewLine, e));
            }
        }

        private static void SendEmail(
            string[] recipients,
            string[] replyTo,
            string subject,
            string body,
            bool isBodyHtml,
            bool hasAttachment,
            string attachmentName,
            byte[] attachmentContent)
        {
            if (subject == null || body == null) return;

            var mail = new MailMessage();

            var from = _smtpUserName;

            mail.From = new MailAddress(@from);
            mail.Subject = subject;
            mail.Body = body;
            mail.IsBodyHtml = isBodyHtml;


            if (recipients != null && recipients.Any())
            {
                mail.To.Clear();

                for (var i = 0; i < recipients.Count(); i++)
                {
                    mail.To.Add(new MailAddress(recipients.ElementAt(i)));
                }
            }

            if (replyTo != null && replyTo.Any())
            {
                mail.CC.Clear();

                for (var i = 0; i < replyTo.Count(); i++)
                {
                    mail.CC.Add(new MailAddress(replyTo.ElementAt(i)));
                }
            }

            if (hasAttachment)
            {
                var contentType = new ContentType
                {
                    Name = attachmentName,
                    MediaType = MediaTypeNames.Application.Octet
                };

                if (attachmentContent.Length > _AttachmentArchivingThresholdKB * 1024) // compress attachments larger than the set archiving threshold
                {
                    var archivingFolder = Directory.CreateDirectory("ArchivingDir");

                    File.WriteAllBytes(Path.Combine(archivingFolder.Name, contentType.Name), attachmentContent);
                    contentType.Name += ".zip";

                    ZipFile.CreateFromDirectory(archivingFolder.Name, contentType.Name);// GZipStream/DeflateStream does not provide a way to include a file name in the header of the zipped stream

                    attachmentContent = File.ReadAllBytes(contentType.Name);
                    archivingFolder.Delete(true);
                    File.Delete(contentType.Name);
                }

                mail.Attachments.Add(new Attachment(new MemoryStream(attachmentContent), contentType));
            }

            var smtpClient = new SmtpClient(_smtpServer)
            {
                Port = _smtpServerPort,
                Credentials = new System.Net.NetworkCredential(_smtpUserName, _smtpPassword),
                EnableSsl = true
            };

            smtpClient.Send(mail);
        }


        public List<SyncStepDescription> GetSteps()
        {
            var steps = LoadSyncSteps();
            var byConfigSteps = ReorderStepsByConfigOrUserOrder(steps).ToList();
            var resultSteps = new List<SyncStepDescription>();

            int executionOrder = 0;
            resultSteps.AddRange(byConfigSteps.Select(i =>
                new SyncStepDescription
                {
                    Id = i.ExecutionOrder,
                    Name = i.ExecutionOrder.ToString(),
                    ExecutionOrder = (executionOrder++),
                    Execute = true,
                }));



            var allSteps = steps.Select(i => new SyncStepDescription { Id = i.ExecutionOrder, Name = i.ExecutionOrder.ToString() });
            foreach (var step in allSteps)
            {
                if (byConfigSteps.FirstOrDefault((i) => i.ExecutionOrder == step.Id) == null)
                {
                    step.ExecutionOrder = (executionOrder++);
                    resultSteps.Add(step);
                }
            }

            return resultSteps.OrderByDescending(i => i.ExecutionOrder).ToList();
        }

        public SyncronizationSessionDTO GetLastSession()
        {
            lock (SyncLock)
            {
                if (_lastSession == null)
                    return null;

                var copy = new SyncronizationSessionDTO();

                copy.LastSync = _lastSession.LastSync;
                copy.Messages.AddRange(_lastSession.Messages);
                copy.Progress = _lastSession.Progress;
                copy.StartDate = _lastSession.StartDate;
                copy.Status = _lastSession.Status;
                copy.Steps.AddRange(_lastSession.Steps);
                copy.ServiceStatus = _serviceStatus.ToString();

                return copy;
            }
        }
    }
}